package ar.com.wholeconsulting.pacc.persistance;

import ar.com.wholeconsulting.pacc.model.*;

import ar.com.wholeconsulting.pacc.model.cabezal.*;
import ar.com.wholeconsulting.pacc.persistance.database.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.persistence.EntityManager;
import javax.persistence.EntityTransaction;
import javax.transaction.Transaction;

public class ModeloParser {

    public List<Importer> importers = new ArrayList<Importer>();
    public EntityManager manager;
    private Set<String> modelos = new HashSet<String>();

    private void doImport() throws ClassNotFoundException {
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String database = "jdbc:odbc:PACC";
            // database+= ";DriverID=22;READONLY=true}";
            Connection conn = DriverManager.getConnection(database, "", "");
            setUp(conn);


            importOthers();

            EntityTransaction transaction = null;


            Statement st = conn.createStatement();
            //PreparedStatement st = conn.prepareStatement("select * from modelos;");
            ResultSet res = st.executeQuery("select * from modelos where modd NOT LIKE '%S/D%';");
            while (res.next()) {
                Modelo mod = new Modelo();
                String usr = res.getString(2).trim();
                String model = res.getString(3).trim();
                String fluid = res.getString(5).trim();
                String fabricante = res.getString(6).trim();
                String acero = res.getString(18).trim();
                acero = acero.isEmpty() ? acero : acero.split("/")[0].trim();
                mod.setNombre(res.getString(4).trim());
                if (modelos.contains(mod.getNombre()) || mod.getNombre().isEmpty()) {
                    continue;
                } else {
                    modelos.add(mod.getNombre());
                }
                mod.setDescripcion(model);
                loadConos(mod, usr, model, conn);
                loadElipticos(mod, usr, model, conn);
                loadEnvolventes(mod, usr, model, conn);
                loadEsfericos(mod, usr, model, conn);
                //                loadMedicionDeEspesores(mod, res, conn);
                //                loadPruevasHidraulicas(mod, res, conn);
                loadTapas(mod, usr, model, conn);
                loadToros(mod, usr, model, conn);
                //                loadValvulasSeguridad(mod, res, conn);
                if (!fluid.isEmpty()) {
                    Fluido fluido = (Fluido) JPASession.getInstance().getSingleResultNamedQuery(Fluido.FIND_BY_NOMBRE, fluid);
                    mod.setFluido(fluido);
                }
                if (!fabricante.isEmpty()) {
                    Fabricante fab = (Fabricante) JPASession.getInstance().getSingleResultNamedQuery(Fabricante.FIND_BY_NOMBRE, fabricante);
                    if (fab != null) {
                        mod.setFabricante(fab);
                        fab.getModelos().add(mod);
                        try {
                            transaction = JPASession.getInstance().getManager().getTransaction();
                            transaction.begin();
                            JPASession.getInstance().getManager().persist(fab);
                            transaction.commit();
                        } catch (Exception ex) {
                            transaction.rollback();
                        }
                    }
                }
                try {
                    transaction = JPASession.getInstance().getManager().getTransaction();
                    transaction.begin();
                    JPASession.getInstance().getManager().persist(mod);
                    transaction.commit();
                } catch (Exception ex) {
                    Logger.getLogger(ModeloParser.class.getName()).log(Level.SEVERE, null, ex);
                    transaction.rollback();
                }
                //manager.persist(fab);
            }
            //transaction.rollback();
            JPASession.getInstance().getManager().close();
        } catch (SQLException e) {
            Logger.getLogger(ModeloParser.class.getName()).log(Level.SEVERE, null, e);
        }
    }

    private void loadConos(Modelo model, String usr, String mod, Connection con) {
        try {
            Statement st = con.createStatement();
            String query = "select * from conos where usuario='" + usr + "' and modelo='" + mod + "';";
            System.out.println(query);
            ResultSet res1 = st.executeQuery(query);
            while (res1.next()) {
                for (int i = 0; i == res1.getInt(3); i++) {
                    CabezalConico cono = new CabezalConico();
                    cono.setDiametroMayor(res1.getDouble(4));
                    cono.setDiametroMenor(res1.getDouble(5));
                    cono.setAltura(res1.getDouble(6));
                    //cono.setAcero((Acero) (JPASession.getInstance().getSingleResultNamedQuery(Acero.FIND_BY_NOMBRE, res.getString(5).trim())));
                    model.getElementos().add(cono);
                }

            }
            res1.close();
            st.close();
        } catch (SQLException ex) {
            Logger.getLogger(ModeloParser.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
        }
    }

    private void loadElipticos(Modelo model, String usr, String mod, Connection con) {
        try {
            Statement st = con.createStatement();
            String query = "select * from eliptico where usuario='" + usr + "' and modelo='" + mod + "';";
            System.out.println(query);
            ResultSet res1 = st.executeQuery(query);
            while (res1.next()) {
                for (int i = 0; i == res1.getInt(4); i++) {
                    CabezalSemieliptico cono = new CabezalSemieliptico();
                    cono.setDiametro(res1.getDouble(3));
                    cono.setPollera(res1.getDouble(5));
                    cono.setDiametroMayor(res1.getDouble(6));
                    cono.setAltura(res1.getDouble(7));
                    cono.setConstanteK(res1.getDouble(8));
                    //cono.setAcero((Acero) (JPASession.getInstance().getSingleResultNamedQuery(Acero.FIND_BY_NOMBRE, res.getString(5).trim())));
                    model.getElementos().add(cono);
                }

            }
            res1.close();
            st.close();
        } catch (SQLException ex) {
            Logger.getLogger(ModeloParser.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private void loadEnvolventes(Modelo model, String usr, String mod, Connection con) {
        try {
            Statement st = con.createStatement();
            String query = "select * from envolvente where usuario='" + usr + "' and modelo='" + mod + "';";
            System.out.println(query);
            ResultSet res1 = st.executeQuery(query);
            while (res1.next()) {
                Envolvente cono = new Envolvente();
                cono.setDiametro(res1.getDouble(3));
                cono.setLongitud(res1.getDouble(4));
                cono.setEficienciaDeJunta(res1.getDouble(9));
                cono.setTensionAdmisible(res1.getLong(10));
                //cono.setAcero((Acero) (JPASession.getInstance().getSingleResultNamedQuery(Acero.FIND_BY_NOMBRE, res.getString(5).trim())));
                model.getElementos().add(cono);
            }
            res1.close();
            st.close();
        } catch (SQLException ex) {
            Logger.getLogger(ModeloParser.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private void loadEsfericos(Modelo model, String usr, String mod, Connection con) {
        try {
            Statement st = con.createStatement();
            String query = "select * from esferico where usuario='" + usr + "' and modelo='" + mod + "';";
            System.out.println(query);
            ResultSet res1 = st.executeQuery(query);
            while (res1.next()) {
                for (int i = 0; i == res1.getInt(4); i++) {
                    CabezalSemiEsferico cono = new CabezalSemiEsferico();
                    cono.setDiametro(res1.getDouble(3));
                    cono.setPollera(res1.getDouble(5));
                    cono.setEficienciaDeJunta(res1.getDouble(8));
                    //cono.setAcero((Acero) (JPASession.getInstance().getSingleResultNamedQuery(Acero.FIND_BY_NOMBRE, res.getString(5).trim())));
                    model.getElementos().add(cono);
                }

            }
            res1.close();
            st.close();
        } catch (SQLException ex) {
            Logger.getLogger(ModeloParser.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private void loadTapas(Modelo model, String usr, String mod, Connection con) {

        try {
            Statement st = con.createStatement();
            String query = "select * from tapas where usuario='" + usr + "' and modelo='" + mod + "';";
            System.out.println(query);
            ResultSet res1 = st.executeQuery(query);
            while (res1.next()) {
                for (int i = 0; i == res1.getInt(6); i++) {
                    CabezalTapaPlana cono = new CabezalTapaPlana();
//                    cono.set(res1.getDouble(3));
//                    cono.setPollera(res1.getDouble(5));
//                    cono.setEficienciaDeJunta(res1.getDouble(12));
//                    cono.setL0(res1.getDouble(6));
//                    cono.setR0(res1.getDouble(7));
//                    cono.setR1(res1.getDouble(8));
//                    cono.setM(res1.getDouble(9));
                    //cono.setAcero((Acero) (JPASession.getInstance().getSingleResultNamedQuery(Acero.FIND_BY_NOMBRE, res.getString(5).trim())));
                    model.getElementos().add(cono);
                }

            }
            res1.close();
            st.close();
        } catch (SQLException ex) {
            Logger.getLogger(ModeloParser.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private void loadToros(Modelo model, String usr, String mod, Connection con) {
        try {
            Statement st = con.createStatement();
            String query = "select * from torisferico where usuario='" + usr + "' and modelo='" + mod + "';";
            System.out.println(query);
            ResultSet res1 = st.executeQuery(query);
            while (res1.next()) {
                for (int i = 0; i == res1.getInt(4); i++) {
                    CabezalTorisferico cono = new CabezalTorisferico();
                    cono.setDiametro(res1.getDouble(3));
                    cono.setPollera(res1.getDouble(5));
                    cono.setEficienciaDeJunta(res1.getDouble(12));
                    cono.setL0(res1.getDouble(6));
                    cono.setR0(res1.getDouble(7));
                    cono.setR1(res1.getDouble(8));
                    cono.setM(res1.getDouble(9));
                    //cono.setAcero((Acero) (JPASession.getInstance().getSingleResultNamedQuery(Acero.FIND_BY_NOMBRE, res.getString(5).trim())));
                    model.getElementos().add(cono);
                }

            }
            res1.close();
            st.close();
        } catch (SQLException ex) {
            Logger.getLogger(ModeloParser.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static void main(String[] args) throws ClassNotFoundException {
        ModeloParser parser = new ModeloParser();
        parser.doImport();
    }

    private void setUp(Connection con) {
        importers.add(new FluidoImporter(con));
        importers.add(new AceroImporter(con));
        importers.add(new FabricanteImporter(con));
        importers.add(new UsuarioImporter(con));
//        System.out.println(JPASession.getInstance().getResultListNamedQuery(Fluido.FIND_ALL).size());
//        System.out.println(JPASession.getInstance().getResultListNamedQuery(Acero.FIND_ALL).size());
//        System.out.println(JPASession.getInstance().getResultListNamedQuery(Fabricante.FIND_ALL).size());

    }

    private void importOthers() {

        for (Importer importer : importers) {
            importer.importaData();
        }

    }
}
